Database Property Example

This example uses the Database property to show how code that used to access ODBC data through the Microsoft Jet database engine can be converted to use ODBCDirect Connection objects.

The OldDatabaseCode procedure uses a Microsoft Jet-connected data source to access an ODBC database.

Sub OldDatabaseCode()

    Dim wrkMain As Workspace
    Dim dbsPubs As Database
    Dim prpLoop As Property

    ' Create Microsoft Jet Workspace object.
    Set wrkMain = CreateWorkspace("", "admin", "", dbUseJet)

    ' Open a Database object based on information in
    ' the connect string.
    Set dbsPubs = wrkMain.OpenDatabase("Publishers", _
        dbDriverNoPrompt, False, _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")

    ' Enumerate the Properties collection of the Database 
    ' object.
    With dbsPubs
        Debug.Print "Database properties for " & _
            .Name & ":"

        On Error Resume Next
        For Each prpLoop In .Properties
            If prpLoop.Name = "Connection" Then
                ' Property actually returns a Connection object.
                Debug.Print "  Connection[.Name] = " & _
                    .Connection.Name
            Else
                Debug.Print "  " & prpLoop.Name & " = " & _
                    prpLoop
            End If
        Next prpLoop
        On Error GoTo 0

    End With

    dbsPubs.Close
    wrkMain.Close

End Sub

The NewDatabaseCode example opens a Connection object in an ODBCDirect workspace. It then assigns the Database property of the Connection object to an object variable with the same name as the data source in the old procedure. None of the subsequent code has to be changed as long as it doesn't use any features specific to Microsoft Jet workspaces.

Sub NewDatabaseCode()

    Dim wrkMain As Workspace
    Dim conPubs As Connection
    Dim dbsPubs As Database
    Dim prpLoop As Property

    ' Create ODBCDirect Workspace object instead of Microsoft 
    ' Jet Workspace object.
    Set wrkMain = CreateWorkspace("", "admin", "", dbUseODBC)

    ' Open Connection object based on information in
    ' the connect string.
    Set conPubs = wrkMain.OpenConnection("Publishers", _
        dbDriverNoPrompt, False, _
        "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")
    ' Assign the Database property to the same object 
    ' variable as in the old code.
    Set dbsPubs = conPubs.Database

    ' Enumerate the Properties collection of the Database 
    ' object. From this point on, the code is the same as the 
    ' old example.
    With dbsPubs
        Debug.Print "Database properties for " & _
            .Name & ":"

        On Error Resume Next
        For Each prpLoop In .Properties
            If prpLoop.Name = "Connection" Then
                ' Property actually returns a Connection object.
                Debug.Print "  Connection[.Name] = " & _
                    .Connection.Name
            Else
                Debug.Print "  " & prpLoop.Name & " = " & _
                    prpLoop
            End If
        Next prpLoop
        On Error GoTo 0

    End With

    dbsPubs.Close
    wrkMain.Close

End Sub